/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package projbd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Matheus
 */
public class DataBaseOperations {

    private static final String sw = "'";
    
    private Connection connection;
    private Statement statement;
    private Statement statement2;
    
    DataBaseOperations(String database) throws SQLException {
        connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/" + database, "postgres", "admin");
        statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
        statement2 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
        
    }
    
    public void update(String id, String value, String attributes, String dataType, boolean abs) throws SQLException {
        String update = new String();      
        
        if (abs) {
            int i;
            for (i = 1; (i < id.length() - 1); i++);
            while (id.charAt(i) != '.') i--;
            String idFather = id.substring(0, i);
            update = "INSERT INTO dewey_edges(dewey,value,attributes,datatype,father_id) VALUES (" + sw + id + sw
                                                                                                   + "," + sw + value + sw
                                                                                                   + "," + sw + attributes + sw
                                                                                                   + "," + sw + dataType + sw
                                                                                                   + "," + sw + idFather + sw + ")";        
        } else {
            String findFather = "SELECT dewey FROM dewey_edges WHERE father_id = " + sw + id + sw;
            ResultSet result = statement.executeQuery(findFather);
            result.last();
            String lastChild = result.getString("dewey");
            
            int i;
            for (i = 1; (i < lastChild.length() - 1); i++);
            while (lastChild.charAt(i) != '.') i--;
            String lastBrother = lastChild.substring((i+1), lastChild.length());
            Long lb = Long.parseLong(lastBrother);
            lb++;
            String newChild = lb.toString();
            String idNew = id + "." + newChild;

            update = "INSERT INTO dewey_edges(dewey,value,attributes,datatype,father_id) VALUES (" + sw + idNew + sw
                                                                                                   + "," + sw + value + sw
                                                                                                   + "," + sw + attributes + sw
                                                                                                   + "," + sw + dataType + sw
                                                                                                   + "," + sw + id + sw + ")";
        }   
        
        try {
            statement.executeUpdate(update);
        } catch (SQLException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(update);
        }
    }

    public void updateOperation() throws SQLException {
        String findActs = "SELECT dewey,father_id FROM dewey_edges WHERE (position('1.6' in dewey) = 1) OR (position('1.7' in dewey) = 1) OR (position('1.8' in dewey) = 1) OR (position('1.9' in dewey) = 1) OR (position('1.10' in dewey) = 1) ORDER BY order_rec;";
        ResultSet acts = null;
        try {
            acts = statement.executeQuery(findActs);
        } catch (SQLException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(findActs);
        }
        acts.first();
        while (!acts.isAfterLast()) {
            String dewey = acts.getString("dewey");
            String father = acts.getString("father_id");
            
            if (dewey.startsWith("1.6", 0)) {
                dewey = dewey.replaceFirst("1.6", "1.16");
                father = father.replaceFirst("1.6", "1.16");
            }
            if (dewey.startsWith("1.7", 0)) { 
                dewey = dewey.replaceFirst("1.7", "1.17");
                father = father.replaceFirst("1.7", "1.17");
            }
            if (dewey.startsWith("1.8", 0)) {
                dewey = dewey.replaceFirst("1.8", "1.18");
                father = father.replaceFirst("1.8", "1.18");
            }
            if (dewey.startsWith("1.9", 0)) {
                dewey = dewey.replaceFirst("1.9", "1.19");
                father = father.replaceFirst("1.9", "1.19");
            }
            if (dewey.startsWith("1.10", 0)) {
                dewey = dewey.replaceFirst("1.10", "1.20");
                father = father.replaceFirst("1.10", "1.20");
            }
            
            acts.updateString("dewey", dewey);
            acts.updateString("father_id", father);
            acts.updateRow();
            
            acts.next();
        }

        int i;
        String findLastAct = "SELECT dewey,value,attributes,datatype FROM dewey_edges WHERE (position('1.20' in dewey) = 1) ORDER BY order_rec;";
        ResultSet r;
        r = statement2.executeQuery(findLastAct);
        for (i = 10; i >= 1; i--) {
            r.first();
            while (!r.isAfterLast()) {
                String dewey, attributes, value, datatype;
                dewey = r.getString("dewey");
                attributes = r.getString("attributes");
                datatype = r.getString("datatype");
                value = r.getString("value");
                value = value.replaceAll("'", "''");
                value = value.replaceAll(",", " ");
                value = value.replace("ACT I", "ACT 0-" + i);
                dewey = dewey.replaceFirst("1.20", "1." + (new Integer(16 - i).toString()));
                update(dewey,value,attributes,datatype,true);
                r.next();
            }
        }
    }
    
    public void delete(String id) {
        String delete = "DELETE FROM dewey_edges CASCADE WHERE dewey = " + sw + id + sw;
        try {
            statement.executeUpdate(delete);
        } catch (SQLException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(delete);            
        }
    }
    
    public void loadTest() throws SQLException {
        String query = "SELECT * FROM dewey_edges WHERE father_id = '1.4500'";
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);        
    }
    
    
    public void query1() throws SQLException {
        String query = "SELECT * FROM dewey_edges WHERE value = 'dblp';";
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);
    }
    
    public void query2() throws SQLException {
        String query = "SELECT * FROM dewey_edges AS a,dewey_edges AS b WHERE (a.father_id = b.dewey) AND "
                + "(a.value = 'incollection') AND (b.value = 'dblp');"
                ;
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);        
    }
    
    public void query3() throws SQLException {
        String query = "SELECT * FROM dewey_edges AS a,dewey_edges AS b,dewey_edges AS c,dewey_edges AS d "
                + "WHERE (a.father_id = b.dewey) AND (b.father_id = c.dewey) AND (c.father_id = d.dewey) AND "
                + "(a.value = 'i') AND (b.value = 'title') AND (c.value = 'article') AND (d.value = 'dblp');"
                ;
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);    
    }

    public void query4() throws SQLException {
        String query = "SELECT * FROM dewey_edges AS a,dewey_edges AS b, dewey_edges AS c "
                + "WHERE (a.father_id = b.dewey) AND (b.father_id = c.dewey) AND (a.value = 'author') AND (c.value = 'dblp');"
                ;
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);        
    }

    public void query5() throws SQLException {
        String query = "SELECT * FROM dewey_edges AS a,dewey_edges AS b WHERE (a.father_id = b.dewey) AND "
                + "(a.value = 'inproceedings') AND (b.value = 'dblp') AND (position('mdate=2006-11-09' in a.attributes) >= 1);"
                ;
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);
    }

    public void query6() throws SQLException {
        String query = "SELECT * FROM dewey_edges AS a,dewey_edges AS b,dewey_edges AS c,dewey_edges AS d "
                + "WHERE (a.father_id = b.dewey) AND (b.father_id = c.dewey) AND (c.father_id = d.dewey) AND "
                + "(a.value = 'Jong-Deok Choi') AND (b.value = 'author') AND (c.value = 'inproceedings') AND (d.value = 'dblp');"
                ;
        ResultSet result = statement.executeQuery(query);
        System.out.println(result);         
    }
    
    public void query7() throws SQLException {
        String query = "SELECT a.value AS avalue,b.dewey AS bdewey,c.dewey AS cdewey,c.value AS cvalue,c.attributes AS cattributes FROM dewey_edges AS a,dewey_edges AS b,dewey_edges AS c,dewey_edges AS d WHERE"
                + " (a.father_id = b.dewey) AND (b.father_id = c.dewey) AND (c.father_id = d.dewey) AND "
                + "(b.value = 'author') AND (c.value = 'inproceedings') AND (d.value = 'dblp') AND (a.value = 'Carlos A. Heuser');"
                ;
        ResultSet result = statement.executeQuery(query);

        boolean found = false;
        String dew = new String();
        String val = new String();
        String att = new String();

        result.first();
        while (!result.isAfterLast() && !found) {
            String brother, name;
            brother = result.getString("bdewey");
            name = result.getString("avalue");

            do {
                brother = brother.substring(1, brother.length());
            } while (brother.contains("."));

            if (brother.equals("3") && name.equals("Carlos A. Heuser")) {
                found = true;

                dew = result.getString("cdewey");
                val = result.getString("cvalue");
                att = result.getString("cattributes");
            }

            result.next();
        }
        System.out.println(dew + val + att);
    }
    
}
